home *** CD-ROM | disk | FTP | other *** search
/ Agent Central Host Computer / Agent - Central Host Computer.iso / _SETUP.1 / vmonthlyriders.sql < prev    next >
Text File  |  2000-05-12  |  780b  |  22 lines
  1. CREATE OR REPLACE VIEW VMONTHLYRIDERS ( REPORT_DATE, 
  2. COUNT_OF_AMOUNTS, TOT_AMT, AUTODUMP, LOW, 
  3. HIGH, DUMPUPR, AUTOHILODUMP, NUM_BUSES
  4.  ) AS SELECT  
  5. /*FAREENTR.FAREBOX_GLID,*/ TRUNC(FAREENTR.CONV_DATE) report_date,  
  6. COUNT(FAREENTR.AMOUNT) count_of_amounts,  
  7. SUM(FAREENTR.AMOUNT) tot_amt, SUM(FAREENTR.AUTO_DUMP) autodump, SUM(FAREENTR.LOW_FARE) low,  
  8. SUM(FAREENTR.HIGH_FARE) high , SUM(FAREENTR.DUMP_UPR) dumpupr,  
  9. SUM(FAREENTR.AUTO_DUMP + FAREENTR.LOW_FARE + FAREENTR.HIGH_FARE + FAREENTR.DUMP_UPR) autohilodump,  
  10. COUNT(DISTINCT BUS.BUS_ID) num_buses  
  11. FROM  
  12. FAREENTR FAREENTR,  
  13. BUS BUS,  
  14. GARAGE GARAGE,  
  15. FAREBOX FAREBOX  
  16. WHERE  
  17. ( FAREENTR.FAREBOX_GLID=FAREBOX.GLID ) AND  
  18. ( FAREBOX.LOCATION=BUS.GLID )  
  19. GROUP BY  
  20. TRUNC(FAREENTR.CONV_DATE);
  21.  
  22.